import sqlite3, datetime
import model, conf

import logging
logging.basicConfig(level=logging.DEBUG)

def createSql(table, cols):
    val = [':' + c for c in cols]
    sql = "insert into {0}({1}) values ({2})".format(table, ",".join(cols), ",".join(val))
    return sql

def lastDate():
    conn = sqlite3.connect(conf.database['dbfile'], detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
    c = conn.cursor()
    c.execute('select max(Date) as "d [date]" from Messages')
    row = c.fetchone()
    print row
    c.close()
    if row != None:
        return row[0]
    else:
        return row

def dbInsert(msg):
    conn = sqlite3.connect(conf.database['dbfile'])
    c = conn.cursor()

    p = [msg.date]
    c.execute("select ID from Messages where Date = ?", p)
    row = c.fetchone()
    if row != None:
        logging.info('Message "' + msg.msg + '" already in db')
        return
    else:
        sql = createSql('Messages', ['Date', 'Subject'])
        try:
            c.execute(sql, [msg.date, msg.msg])
            conn.commit()
        except Exception as e:
            logging.error(e)
            return
        msgid = c.lastrowid

    for account in msg.accounts:
        params = dict(zip(account.keys, account.values))
        p = [params['nr_rachunku']]
        c.execute("select ID from Accounts where Nr_rachunku = ?", p)
        row = c.fetchone()
        if row != None:
            accid = row[0]
        else:
            p = account.keys
            sql = createSql('Accounts', p)
            p = account.values
            c.execute(sql, p)
            conn.commit()
            accid = c.lastrowid
        count = 0

        for t in account.transactions:
            val = t.values
            p = ['AccountId', 'MessageId']
            p.extend(t.keys)
            sql = createSql('Transactions', p)
            v = [accid, msgid]
            v.extend(val)
            c.execute(sql, v)
            conn.commit()
            count = count + 1
        logging.info('inserted> ' + str(count))
    c.close()
